package package1;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * reading and writing excel using POI
 * 
 * @author Alvin
 */
public class TestForExcelFile {
	@SuppressWarnings("unused")
	public static void main(String[] args) throws Exception {
		
//		 readExcelFile(fileName);
		// createExcelFile();
//		getHyperLink();
		
		String fileAddr = getDesktop()+"test\\test.xlsx";
		String folderAddr = getDesktop()+"test";
		readname(fileAddr,folderAddr);
//		rename(fileAddr,folderAddr);
		
		System.err.println("over");
		
	}

	/**
	 * ok to public use
	 */
	public static void readname(String fileAddr, String folderAddr) throws IOException {
		File file = new File(fileAddr);
		System.err.println("file:"+fileAddr);
		Workbook wb;
		FileOutputStream fos ;
		if(!file.exists()){
			wb = new XSSFWorkbook();
			wb.createSheet();
			fos = new FileOutputStream(file);
			wb.write(fos);
			fos.close();
		}
		File[] files = new File(folderAddr).listFiles();
		wb = getWorkbook(file);
		Sheet sheet = wb.getSheetAt(0);
		if(sheet.getRow(0)==null)
			sheet.createRow(0);
		int lastColumn = getLastColumn(sheet.getRow(0));
		setCell(sheet,0,lastColumn+1,new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
		for(int i=0;i<files.length;i++){
			setCell(sheet,i+1,lastColumn+1,files[i].getName());
		}
		fos = new FileOutputStream(file);
		wb.write(fos);
		fos.close();
		open(file);
	}

	/**
	 * ok to public use
	 */
	public static void rename(String fileAddr, String folderAddr) throws IOException {
		File file= new File(fileAddr);
		if(!file.exists()) file.createNewFile();
		File[] files = new File(folderAddr).listFiles();
		Workbook wb = getWorkbook(file);
		Sheet sheet = wb.getSheetAt(0);
		Map<String,String> nameMap = new HashMap<String,String>();
		int lastColumn = getLastColumn(sheet.getRow(0));
		for(int i=0;i<sheet.getLastRowNum()+1;i++){
			Cell c1 = getCell(sheet, i, lastColumn-1);//left cell-old name
//			print(c1);
			Cell c2 = getCell(sheet, i, lastColumn);//right cell-new name
//			print(c2);
			nameMap.put(c1.getStringCellValue(), c2.getStringCellValue());
		}
		for(File f:files){
			if(nameMap.containsKey(f.getName()))
				f.renameTo(new File(f.getParentFile(),nameMap.get(f.getName())));
		}
	}

	private static int getLastColumn(Row row) {
		return row.getPhysicalNumberOfCells()-1;
	}

	private static String open(File f) throws IOException {
		java.awt.Desktop.getDesktop().open(f);
		return f.getAbsolutePath();
	}

	private static String open(String f) throws IOException {
		open(new File(f));
		return f;
	}

	public static void setCell(Sheet sheet, int row, int column, String s) {
		if(sheet.getRow(row)==null)
			sheet.createRow(row);
		Row r = sheet.getRow(row);
		if(r.getCell(column)==null)
			r.createCell(column);
		Cell c = r.getCell(column);
		c.setCellValue(s);
	}

	public static void print(Cell c) {
		if(c!=null)System.err.println("("+c.getColumnIndex()+","+c.getRowIndex()+")"+c.getStringCellValue());
	}

	public static Cell getCell(Sheet sheet, int i, int j) {
		Row row = sheet.getRow(i);
		if(row!=null)
			return sheet.getRow(i).getCell(j);
		else
			return null;
	}

	/**
	 * attention, if there is blank cell, it does not count into the physical number
	 * @param sheet
	 * @return
	 */
	public static int getRowNumbers(Sheet sheet) {
		return sheet.getPhysicalNumberOfRows();
	}

	public static Workbook getWorkbook(File file) throws IOException {
		InputStream is = new FileInputStream(file);
		Workbook wb = null;
		if (file.getName().endsWith(".xlsx"))// 2007 .xlsx
			wb = new XSSFWorkbook(is);
		else if (file.getName().endsWith(".xls"))// 2003 .xls
			wb = new HSSFWorkbook(is);
		else
			throw new IOException("invalid file for excel");
		return wb;
	}

	private static String getDesktop() {
		return System.getenv("USERPROFILE") + "\\desktop\\";
	}
	
	private static void getHyperLink() throws IOException {
		InputStream is = new FileInputStream(new File("C:/Users/wangyic/Desktop/test.xlsx"));
		XSSFWorkbook wb = new XSSFWorkbook(is);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFCell cell = sheet.getRow(0).getCell(0);
		XSSFCreationHelper helper = (XSSFCreationHelper) wb.getCreationHelper();
		XSSFHyperlink link = helper.createHyperlink(org.apache.poi.common.usermodel.Hyperlink.LINK_URL);
		link.setAddress("https://www.baidu.com");
		cell.setHyperlink(link);
		XSSFCellStyle linkStyle = cell.getCellStyle();
		XSSFFont cellFont = (XSSFFont) wb.createFont();
		cellFont.setUnderline((byte) 1);
		cellFont.setColor(IndexedColors.GREEN.getIndex());// IndexedColors.YELLOW.getIndex()
		linkStyle.setFont(cellFont);
		OutputStream os = new FileOutputStream(new File("C:/Users/wangyic/Desktop/test.xlsx"));
		wb.write(os);
		os.flush();
		os.close();
		System.err.println("succeed!");
	}

	@SuppressWarnings({ "unused", "resource" })
	private static void readExcelFile(String fileName) throws FileNotFoundException, IOException, Exception {
		InputStream is = new FileInputStream(new File(fileName));
		Workbook wb = null;
		if (fileName.endsWith(".Dxlsx"))// 2007 .xlsx
			wb = new XSSFWorkbook(is);
		else if (fileName.endsWith(".xls"))// 2003 .xls
			wb = new HSSFWorkbook(is);
		else
			throw new Exception("invalid file for excel");
		Sheet sheet = wb.getSheetAt(0);
		System.err.println("total number for valid rows:" + sheet.getPhysicalNumberOfRows());
		Iterator<Row> ir = sheet.rowIterator();
		while (ir.hasNext()) {
			Row row = ir.next();
			int rowNum = row.getRowNum();
			int cellNum = row.getPhysicalNumberOfCells();
			System.err.println("@Row:" + rowNum);
			System.err.println("cellNumber:" + cellNum);
			Iterator<Cell> ic = row.cellIterator();
			while (ic.hasNext()) {
				Cell c = ic.next();
				String content = c.toString();
				System.err.println("cell content:" + content);
			}
		}
		is.close();
	}

	@SuppressWarnings("unused")
	private static void createExcelFile() throws FileNotFoundException, IOException {
		FileOutputStream fos = new FileOutputStream("321.xlsx");
		XSSFWorkbook wb2 = new XSSFWorkbook();
		XSSFSheet sheet = wb2.createSheet("sheet001");
		wb2.createSheet();
		XSSFRow r2 = sheet.createRow(0);
		XSSFCell c2 = r2.createCell(0);
		c2.setCellValue("123");
		wb2.createSheet("sheet002").createRow(8).createCell(4).setCellValue("nihaoya");
		wb2.write(fos);
		fos.flush();
		fos.close();
		System.err.println("success");
	}

}
